package export.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import loader.persister.ConnectionManager;
import elements.Constants;
import elements.Deal;
import elements.Parameter;
import elements.Shop;

public class Loader {
	private Connection conn;
	
	public Loader() {
		conn = new ConnectionManager().getConnection();
	}

	public List<Deal> loadDeals(String startTime, String endTime,
			String discountPercent, String cityName, String category,
			String title, String price, String websiteId, String members,
			int offset, int length, String orderBy, String order) {
		String sql = "select id, websiteId, oid, title, dealUrl, sImageUrl, mImageUrl, lImageUrl, cityName, category, tips, price, dealValue, discountPercent, startTime, endTime from deal";
		List<Object> parameters = new ArrayList<Object>();
		
		sql += generateSql(parameters, startTime, endTime, discountPercent, cityName, category, title, price, websiteId, members);
		
		if (orderBy != null) {
			sql += " order by " + orderBy;
		}
		if (order != null && order.equalsIgnoreCase("desc")) {
			sql += " desc";
		}
		
		if (length > 0) {
			sql += " limit ?,?";
			parameters.add(offset);
			parameters.add(length);
		}
		
		
		//debug information
		//System.out.println("search sql: " + sql);
		//System.out.println("p size: " + parameters.size());
		
		List<Deal> deals = new ArrayList<Deal>();
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			
			for (int i = 0; i < parameters.size(); i++) {
				Object o = parameters.get(i);
				if (o instanceof String) {
					pstmt.setString(i + 1, (String)o);
				}
				else if (o instanceof Double)
					pstmt.setDouble(i + 1, (Double)o);
				else if (o instanceof Long)
					pstmt.setLong(i + 1, (Long)o);
				else if (o instanceof Integer)
					pstmt.setInt(i + 1, (Integer)o);
				else
					System.out.println("other type");
			}
			ResultSet result = pstmt.executeQuery();
			while (result.next()) {
				long dealId = result.getLong("id");
				Deal deal = new Deal();
				deal.setWebsiteId(result.getInt("websiteId"));
				deal.setOid(result.getString("oid"));
				deal.setTitle(result.getString("title"));
				deal.setDealUrl(result.getString("dealUrl"));
				deal.setSImageUrl(result.getString("sImageUrl"));
				deal.setMImageUrl(result.getString("mImageUrl"));
				deal.setLImageUrl(result.getString("lImageUrl"));
				deal.setCityName(result.getString("cityName"));
				deal.setCategory(result.getString("category"));
				deal.setTips(result.getString("tips"));
				deal.setPrice(result.getDouble("price"));
				deal.setDealValue(result.getDouble("dealValue"));
				deal.setDiscountPercent(result.getDouble("discountPercent"));
				deal.setStartTime(result.getLong("startTime"));
				deal.setEndTime(result.getLong("endTime"));
				
				String sqlForShop = "select shopName, shopUrl, shopPhone, shopAddress, shopArea, longtitude, latitude from shop where dealId = " + dealId;
				ResultSet shopResult = conn.createStatement().executeQuery(sqlForShop);
				List<Shop> shops = new ArrayList<Shop>();
				while (shopResult.next()) {
					Shop shop = new Shop();
					shop.setShopName(shopResult.getString("shopName"));
					shop.setShopUrl(shopResult.getString("shopUrl"));
					shop.setShopPhone(shopResult.getString("shopPhone"));
					shop.setShopAddress(shopResult.getString("shopAddress"));
					shop.setShopArea(shopResult.getString("shopArea"));
					shop.setLongtitude(shopResult.getDouble("longtitude"));
					shop.setLatitude(shopResult.getDouble("latitude"));
					
					shops.add(shop);
				}
				shopResult.close();
				deal.setShops(shops);
				deals.add(deal);
			}
			result.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return deals;
	}

	private String generateSql(List<Object> odlParameters, String startTime, String endTime,
			String discountPercent, String cityName, String category,
			String title, String price, String websiteId, String members) {
		String sql = "";
		List<Object> parameters = new ArrayList<Object>();
		if (startTime != null) {
			parameters.add(Long.parseLong(startTime));
			sql += " and startTime > ?";
		}
		if (endTime != null) {
			parameters.add(Long.parseLong(endTime));
			sql += " and endTime > ?";
		}
		if (discountPercent != null) {
			parameters.add(Double.parseDouble(discountPercent));
			sql += " and discountPercent <= ?";
		}
		if (cityName != null) {
			String[] cityNames = cityName.split(",");
			if (cityNames.length > 0) {
				parameters.add("%" + cityNames[0] + "%");
				sql += " and (cityName like ?";
			}
			for (int i = 1; i < cityNames.length; i++) {
				parameters.add("%" + cityNames[i] + "%");
				sql += " or cityName like ?";
			}
			sql += ")";
		}
		if (category != null){
			parameters.add(category);
			sql += " and category = ?";
		}
		if (title != null){
			parameters.add("%" + title + "%");
			sql += " and title like ?";
		}
		if (price != null){
			parameters.add(Double.parseDouble(price));
			sql += " and price < ?";
		}
		odlParameters.addAll(parameters);
		if (parameters.size() == 0)
			return "";
		return " where" + sql.substring(4);
	}
}
